package dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.DataBaseConnection;

import bean.Producto;
import dao.Entidad;

public class MySQLProductoDAO implements Entidad<Producto>{

	@Override
	public Producto insert(Producto t) {
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="INSERT INTO producto(nomb_prod,mode_prod,";
			sql=sql + "stoc,desc_prod,prec,gara,id_marc) values(?,?,?,?,?,?,?)";
			PreparedStatement ps=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, t.getNomb_prod());
			ps.setString(2, t.getMode_prod());
			ps.setInt(3, t.getStoc());
			ps.setString(4, t.getDesc_prod());
			ps.setDouble(5, t.getPrec());
			ps.setDouble(6, t.getGara());
			ps.setInt(7, t.getId_marc());
			ps.executeUpdate();
			ResultSet rs=ps.getGeneratedKeys();
			if(rs.next()){
				int id=rs.getInt(1);
				t.setId_prod(id);
			}		
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return t;
	}

	@Override
	public int update(Producto t) {
		Connection con=null;
		int rows = -1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="UPDATE producto SET nomb_prod=?,mode_prod=?,stoc=?,desc_prod=?,prec=?,gara=?,id_marc=?,esta=?";
			sql = sql +	" WHERE id_prod= ?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, t.getNomb_prod());
			ps.setString(2, t.getMode_prod());
			ps.setInt(3, t.getStoc());
			ps.setString(4, t.getDesc_prod());
			ps.setDouble(5, t.getPrec());
			ps.setDouble(6, t.getGara());
			ps.setInt(7, t.getId_marc());
			ps.setString(8, t.getEsta());
			ps.setInt(9, t.getId_prod());
			rows =ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;	
	}

	@Override
	public int delete(Producto t) {
		Connection con=null;
		int rows=-1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="UPDATE producto set esta='N' WHERE id_prod=?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_prod());
			rows=ps.executeUpdate();
			System.out.println(rows);
				
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;
	}

	@Override
	public Producto findById(Producto t) {
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="SELECT * FROM producto WHERE id_prod=? and estado ='R'";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_prod());
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				t.setId_prod(rs.getInt("id_prod"));
				t.setNomb_prod(rs.getString("nomb_prod"));
				t.setMode_prod(rs.getString("modelo"));
				t.setStoc(rs.getInt("stoc"));
				t.setDesc_prod(rs.getString("desc_prod"));
				t.setPrec(rs.getDouble("prec"));
				t.setGara(rs.getDouble("gara"));
				t.setId_marc(rs.getInt("id_marc"));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return t;
	}

	@Override
	public List<Producto> listAll() {
		List<Producto> productos = new ArrayList<Producto>();
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="Select * from producto where esta = 'R'";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				Producto t = new Producto();
				t.setId_prod(rs.getInt("id_prod"));
				t.setNomb_prod(rs.getString("nomb_prod"));
				t.setMode_prod(rs.getString("mode_prod"));
				t.setStoc(rs.getInt("stoc"));
				t.setDesc_prod(rs.getString("desc_prod"));
				t.setPrec(rs.getDouble("prec"));
				t.setGara(rs.getDouble("gara"));
				t.setId_marc(rs.getInt("id_marc"));
				productos.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return productos;
	}

	@Override
	public List<Producto> listAllCustom(Object... objects) {
		// TODO Auto-generated method stub
		return null;
	}

}
